package com.googlecode.maratische.google.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.googlecode.maratische.google.BaseException;
import com.googlecode.maratische.google.dao.BaseDao;
import com.googlecode.maratische.google.model.Model;

public abstract class BaseDaoImpl<T extends Model> implements BaseDao<T> {
	static Logger logger = getLogger();
	public static final String DATE_TIME_FORMAT_SQL = "yyyy-MM-dd HH:mm:ss";
	
	protected static Logger getLogger() {
		return Logger.getLogger(BaseDaoImpl.class.getName());
	}
	
	protected Connection conn;
	
	public BaseDaoImpl(Connection conn) {
		this.conn = conn;
	}

	
	protected PreparedStatement getPreparedStatement = null;
	protected PreparedStatement generateGetPreparedStatement() throws SQLException {
		if (getPreparedStatement == null) {
			getPreparedStatement = this.conn.prepareStatement("select * from "+getTableName()+" where ID = ?");
		}
		return getPreparedStatement;
	}
	public T get(Integer id) throws SQLException, BaseException {
		PreparedStatement preparedStatement = generateGetPreparedStatement();
		preparedStatement.setInt(1, id);
		ResultSet rs = preparedStatement.executeQuery();
//		String sql = "select * from "+getTableName()+" where ID = "+id;
//		Statement statement = this.conn.createStatement();
//		ResultSet rs = statement.executeQuery(sql);
		if (rs.next()) {
			return createByRow(rs);
		}
		return null;
	}

	public ArrayList<T> list()  throws SQLException, BaseException {
		return list(null,null);
	}
	
	public ArrayList<T> list(String order)  throws SQLException, BaseException {
		return list(null,order);		
	}

	public ArrayList<T> list(String where,String order, Object... values) throws SQLException, BaseException {
		ArrayList<T> result = new ArrayList<T>();
		String sql = "select * from "+getTableName()+" ";
		PreparedStatement statement = generateListQuery(where, order, sql, values);
		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			result.add(createByRow(rs));
		}
		return result;
	}

	/**
	 * полностью подготавливается запрос в базу данных
	 * @param where
	 * @param order
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement generateListQuery(String where, String order, String sql, Object... values) throws SQLException {
		if (where != null && where.length()>0) {
			sql += " where "+where;
		}
		if (order != null && order.length()>0) {
			sql += " order by "+order;
		}
		PreparedStatement statement = this.conn.prepareStatement(sql);
		if (values != null && values.length>0) {
			int i=0;
			for (Object object : values) {
				i++;
				if (object == null) {
					statement.setNull(i, Types.OTHER);
					/**
					 * FIXME Types.OTHER - ацкий эксперимент!
					 */
				}
				else if (object instanceof Integer) {
					statement.setInt(i, (Integer)object);
				}
				else if (object instanceof String) {
					statement.setString(i, (String)object);
				}
				else if (object instanceof Date) {
					statement.setDate(i, (Date)object);
				}
			}
		}
		return statement;
	}
	
	public boolean remove(T object) throws BaseException {
		if (object != null) {
			return this.remove(object.getId());
			/*String sql = "delete from "+getTableName()+" where id = "+object.getId();
			try {
				Statement stat = this.conn.createStatement();
				stat.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}*/
		}
		return false;
	}
	
	
	protected PreparedStatement removePrepareStatement = null;
	protected PreparedStatement generateRemovePreparedStatement() throws SQLException {
		if (removePrepareStatement == null) {
			removePrepareStatement = this.conn.prepareStatement("delete from "+getTableName()+" where id = ? ");
		}
		return removePrepareStatement;
	}
	public boolean remove(Integer id) throws BaseException {
		if (id != null) {
			PreparedStatement preparedStatement;
			try {
				preparedStatement = generateRemovePreparedStatement();
				preparedStatement.setInt(1, id);
				return preparedStatement.executeUpdate() >0 ? true : false;
			} catch (SQLException e) {
				throw new BaseException("mainframe.error.remove.element "+id);
			}
//			String sql = "delete from "+getTableName()+" where id = "+id;
//			try {
//				Statement stat = this.conn.createStatement();
//				return stat.executeUpdate(sql) >0 ? true : false;
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
		}
		return false;
	}
	
	public T save(T object) throws SQLException, BaseException {
		if (object != null) {
			if (object.getId() == null || object.getId()==0) {
				PreparedStatement pStat = generateInsertPreparedStatement();
				pStat = putInsertPrepareStatement(pStat,object);
				pStat.execute();
//				Statement stat = this.conn.createStatement();
//				String sql = "select  IDENTITY_VAL_LOCAL() from "+getTableName();
//				ResultSet rs = stat.executeQuery(sql);
				PreparedStatement identityPreparedStatement = generateIdentityPreparedStatement();
				ResultSet rs = identityPreparedStatement.executeQuery();
				if (rs != null && rs.next()) {
					object.setId(rs.getInt(1));
				}
			} else {
				PreparedStatement pStat = generateUpdatePreparedStatement();
				pStat = putUpdatePrepareStatement(pStat,object);
				pStat.execute();
			}
			if (object != null && object.getId() != null) {
				object = get(object.getId());
			}
		}
		return object;
	}
	
	protected PreparedStatement identityPrepareStatement = null;
	protected PreparedStatement generateIdentityPreparedStatement() throws SQLException {
		if (identityPrepareStatement == null) {
			identityPrepareStatement = this.conn.prepareStatement("select  IDENTITY_VAL_LOCAL() from "+getTableName());
		}
		return identityPrepareStatement;
	}
	
	protected PreparedStatement insertPreparedStatement = null;
	protected PreparedStatement generateInsertPreparedStatement() throws SQLException {
		if (insertPreparedStatement == null) {
			insertPreparedStatement = this.conn.prepareStatement(generateInsertSql());
		}
		return insertPreparedStatement;
	}
	
	protected PreparedStatement updatePreparedStatement = null;
	protected PreparedStatement generateUpdatePreparedStatement() throws SQLException {
		if (updatePreparedStatement == null) {
			updatePreparedStatement = this.conn.prepareStatement(generateUpdateSql());
		}
		return updatePreparedStatement;
	}
	
	abstract protected PreparedStatement putInsertPrepareStatement(PreparedStatement pStat, T status) throws SQLException;
	abstract protected PreparedStatement putUpdatePrepareStatement(PreparedStatement pStat, T status) throws SQLException;
	
	protected Boolean intToBoolean(int i) {
		return (i == 1 ? Boolean.TRUE : Boolean.FALSE);
	}
	
	protected int booleanToInt(Boolean b) {
		return (b != null && b.booleanValue() ? 1 : 0);
	}

	protected String getStringWithQuotesOrNull(String text) {
		if (text == null) {
			return "null";
		}
		return "'"+text+"'";
	}
	
	protected String getTIMESTAMPWithQuotesOrNull(Date date) {
		if (date == null) {
			return "null";
		}
		Timestamp timestamp = new Timestamp(date.getTime());
		return "'" +timestamp.toString() +"'";
//		return "'" +new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.f...").format(date) +"'";
	}
	
	
	public abstract T createByRow(ResultSet rs) throws SQLException, BaseException ;
	
	abstract protected String getTableName() ;
	
	protected abstract String generateUpdateSql();

	protected abstract String generateInsertSql();
	
}
